Extract Data from JSON File 11

Extract Data from JSON File

Step 1: Now go to SSIS Toolbox and drag and drop "Data Flow Task" to Control Flow and rename it.

*Reminder: If you don’t see the SSIS Toolbox, go to View à Other Windows à SSIS Toolbox or Right click on an empty space inside the Control Flow or Data Flow and select SSIS Toolbox.

Step 2: Double click on Data Flow Task then drag and drop Script Component to Data Flow.

We’ll be asked what type of Script Component we want to add. Choose Source and click the OK button.


Step 3: Double-click on the Script Componentgo to the Inputs and Outputs tab. Expand the Output 0 nodeclick on the Output Columns and add 40 columns by clicking on the Add Column button

 Step 4: Change the names of all the columns by going over to the Common Properties panel on the right then go to Name and replace Column…., with the proper column name

**Reminder: All the data types for each column keep them as String with the length of 255. We will change these data types later in the steps.



Step 5: Go to the Script tab and click the Edit Script… button.

Go to Solution Explorer à References à Right click à Add Reference… and select System.Web.Extensions and System.Windows.Forms library.


Step 6: Right click on the solution name in Solution Explorer à Add à Class and name the class GunViolence. Copy the below code to put in GunViolence.csClick  to save.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace GunViolenceNameSpace
{
    class GunViolence
    {
        public string Date { get; set; }
        public string School { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string KilledShooter { get; set; }
        public string WoundedVictims { get; set; }
        public string KilledVictims { get; set; }
        public string GenderOfVictims { get; set; }
        public string VictimAffiliationWithSchool { get; set; }
        public string VictimAge { get; set; }
        public string VictimRace { get; set; }
        public string VictimEthnicity { get; set; }
        public string TargetedSpecificVictim { get; set; }
        public string RandomVictims { get; set; }
        public string Bullied { get; set; }
        public string DomesticViolence { get; set; }
        public string SuicideByShooter { get; set; }
        public string ShooterActions { get; set; }
        public string PrePlannedSchoolAttack { get; set; }
        public string Category { get; set; }
        public string SchoolType { get; set; }
        public string TimeOfOccurrence { get; set; }
        public string DurationInMinutes { get; set; }
        public string DayOfWeek { get; set; }
        public string IsSchoolDay { get; set; }
        public string TimePeriod { get; set; }
        public string DuringSportingEvent { get; set; }
        public string DuringSchoolSponsoredEvent { get; set; }
        public string Location { get; set; }
        public string NumberOfShotsFired { get; set; }
        public string FirearmType { get; set; }
        public string NumberOfShooters { get; set; }
        public string ShooterName { get; set; }
        public string ShooterAge { get; set; }
        public string ShooterGender { get; set; }
        public string ShooterRace { get; set; }
        public string ShooterEthnicity { get; set; }
        public string ShooterAffiliationWithSchool { get; set; }
        public string ShooterAccomplianceFired { get; set; }
        public string HostagesTaken { get; set; }
    }
}

Step 7 :  Add below code in the main.cs. Click  to save.

String jsonFileContent = File.ReadAllText(@"D:\ERUM\ETLProject1\SourcesEtlProject1\csvjson.json");
        JavaScriptSerializer js = new JavaScriptSerializer() { MaxJsonLength = 86753090 };
        List<GunViolence> ViolenceListV = js.Deserialize<List<GunViolence>>(jsonFileContent);
        foreach (GunViolence GunViolenceList in ViolenceListV)
        {
            Output0Buffer.AddRow();
            Output0Buffer.Date = GunViolenceList.Date;
            Output0Buffer.School = GunViolenceList.School;
            Output0Buffer.City = GunViolenceList.City;
            Output0Buffer.Date = GunViolenceList.Date;
            Output0Buffer.State = GunViolenceList.State;
            Output0Buffer.KilledShooter = GunViolenceList.KilledShooter;
            Output0Buffer.WoundedVictims = GunViolenceList.WoundedVictims;
            Output0Buffer.KilledVictims = GunViolenceList.KilledVictims;
            Output0Buffer.GenderOfVictims = GunViolenceList.GenderOfVictims;
            Output0Buffer.VictimAffiliationWithSchool = GunViolenceList.VictimAffiliationWithSchool;
            Output0Buffer.VictimAge = GunViolenceList.VictimAge;
            Output0Buffer.VictimRace = GunViolenceList.VictimRace;
            Output0Buffer.VictimEthnicity = GunViolenceList.VictimEthnicity;
            Output0Buffer.TargetedSpecificVictim = GunViolenceList.TargetedSpecificVictim;
            Output0Buffer.RandomVictims = GunViolenceList.RandomVictims;
            Output0Buffer.Bullied = GunViolenceList.Bullied;
            Output0Buffer.DomesticViolence = GunViolenceList.DomesticViolence;
            Output0Buffer.SuicideByShooter = GunViolenceList.SuicideByShooter;
            Output0Buffer.ShooterActions = GunViolenceList.ShooterActions;
            Output0Buffer.PrePlannedSchoolAttack = GunViolenceList.PrePlannedSchoolAttack;
            Output0Buffer.Category = GunViolenceList.Category;
            Output0Buffer.SchoolType = GunViolenceList.SchoolType;
            Output0Buffer.TimeOfOccurrence = GunViolenceList.TimeOfOccurrence;
            Output0Buffer.DurationInMinutes = GunViolenceList.DurationInMinutes;
            Output0Buffer.DayOfWeek = GunViolenceList.DayOfWeek;
            Output0Buffer.IsSchoolDay = GunViolenceList.IsSchoolDay;
            Output0Buffer.TimePeriod = GunViolenceList.TimePeriod;
            Output0Buffer.DuringSportingEvent = GunViolenceList.DuringSportingEvent;
            Output0Buffer.DuringSchoolSponsoredEvent = GunViolenceList.DuringSchoolSponsoredEvent;
            Output0Buffer.Location = GunViolenceList.Location;
            Output0Buffer.NumberOfShotsFired = GunViolenceList.NumberOfShotsFired;
            Output0Buffer.FirearmType = GunViolenceList.FirearmType;
            Output0Buffer.NumberOfShooters = GunViolenceList.NumberOfShooters;
            Output0Buffer.ShooterName = GunViolenceList.ShooterName;
            Output0Buffer.ShooterAge = GunViolenceList.ShooterAge;
            Output0Buffer.ShooterGender = GunViolenceList.ShooterGender;
            Output0Buffer.ShooterRace = GunViolenceList.ShooterRace;
            Output0Buffer.ShooterEthnicity = GunViolenceList.ShooterEthnicity;
            Output0Buffer.ShooterAffiliationWithSchool = GunViolenceList.ShooterAffiliationWithSchool;
            Output0Buffer.ShooterAccomplianceFired = GunViolenceList.ShooterAccomplianceFired;
            Output0Buffer.HostagesTaken = GunViolenceList.HostagesTaken;
        }
    }
}


What is JSON?

JSON stands for JavaScript Object Notation

JSON is a lightweight format for storing and transporting data

JSON is often used when data is sent from a server to a web page

JSON is "self-describing" and easy to understand

Click the link to read more about JSON

https://www.w3schools.com/whatis/whatis_json.asp